package com.westvalley.tools.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.google.common.collect.Lists;
import com.westvalley.common.kit.EntityKit;
import com.westvalley.common.kit.StringKit;
import com.westvalley.tools.excel.annotation.FieldTitleHelp;
import com.westvalley.tools.excel.bean.BeanModel;
import com.westvalley.tools.excel.exception.ExcelException;
import com.westvalley.tools.excel.result.ImprotExcelResult;

import weaver.general.BaseBean;

/**
 * 通用Excel数据导入工具类
 * @author mos
 *
 */
public class ImpExcel extends BaseBean {
	
	/**
	 * 解析excel文件
	 * @param filePath
	 * @param sheetIndex
	 * @param clazz
	 * @return
	 * @throws ExcelException
	 */
	public static ImprotExcelResult getExcelData(String filePath,int sheetIndex,Class<? extends BeanModel> clazz) throws ExcelException {
		InputStream stream = null;
		try{
			stream = new FileInputStream(filePath);
		}catch(FileNotFoundException e){
			throw new ExcelException("导入文件不存在！",e);
		}
		return getExcelData(stream,sheetIndex,clazz);
	}
	
	/**
	 * 解析excel文件
	 * @param file
	 * @param sheetIndex
	 * @param clazz
	 * @return
	 * @throws ExcelException
	 */
	public static ImprotExcelResult getExcelData(File file,int sheetIndex,Class<? extends BeanModel> clazz) throws ExcelException {
		InputStream stream = null;
		try{
			stream = new FileInputStream(file);
		}catch(FileNotFoundException e){
			throw new ExcelException("导入文件不存在！",e);
		}
		return getExcelData(stream,sheetIndex,clazz);
	}
	
	/**
	 * 解析excel文件
	 * @param stream
	 * @param index
	 * @param clazz
	 * @return
	 * @throws ExcelException
	 */
	public static ImprotExcelResult getExcelData(InputStream stream,int sheetIndex,Class<? extends BeanModel> clazz) throws ExcelException{
		try{
			HSSFWorkbook localHSSFWorkbook = new HSSFWorkbook(new POIFSFileSystem(stream));
			HSSFSheet localHSSFSheet = localHSSFWorkbook.getSheetAt( sheetIndex );
			if(localHSSFSheet!=null){
				List<Object> titleList = FieldTitleHelp.getFieldTexts(clazz);
				List<Object> excelTitleList = getExcelTitle(localHSSFSheet);
				if( titleList!=null && excelTitleList!=null && titleList.equals(excelTitleList) ){
					List<BeanModel> dataList = Lists.newArrayList();
					for(List<Object> rowData : getExcelMainData(localHSSFSheet)){
						try {
							dataList.add(list2Entity(rowData,clazz));
						} catch (Exception e) {
							throw new ExcelException("实体对象创建异常！",e);
						}
					}
					return ImprotExcelResult.me(titleList, excelTitleList, dataList);
				}else{
					throw new ExcelException("导入模板错误！");
				}
			}
		}catch(IOException e){
			throw new ExcelException("导入文件错误！",e);
		}
		return null;
	}
	
	/**
	 * 获取表头
	 * @param localHSSFSheet
	 * @return
	 * @throws ExcelException 
	 */
	private static List<Object> getExcelTitle(HSSFSheet localHSSFSheet) throws ExcelException{
		return getExcelRowData(localHSSFSheet,0);
	}
	
	/**
	 * 获取excel数据
	 * @param localHSSFSheet
	 * @return
	 * @throws ExcelException 
	 */
	private static List<List<Object>> getExcelMainData(HSSFSheet localHSSFSheet) throws ExcelException{
		List<List<Object>> excelList = Lists.newArrayList();
		if(localHSSFSheet!=null){
			for(int i=1;i<=localHSSFSheet.getLastRowNum();i++){
				List<Object> list = getExcelRowData(localHSSFSheet,i);
				excelList.add(list);
			}
		}
		return excelList;
	}
	
	
	/**
	 * 获取某行数据
	 * @param localHSSFSheet
	 * @param rowIndex
	 * @return
	 * @throws ExcelException 
	 */
	private static List<Object> getExcelRowData(HSSFSheet localHSSFSheet,int rowIndex) throws ExcelException{
		List<Object> rowList = Lists.newArrayList();
		if(localHSSFSheet!=null){
			HSSFRow row = localHSSFSheet.getRow(rowIndex);
			if(row!=null){
				int temp1 = row.getLastCellNum();
				int temp2 = row.getPhysicalNumberOfCells();
				int cellNum = temp1;
				if(temp1>temp2){
					cellNum = temp1;
				}else if(temp2>temp1){
					cellNum = temp2;
				}
				for(short k=0; k<cellNum; k++){
					HSSFCell cell = row.getCell(k);
					rowList.add( getCellValue(cell) );
				}
			}else{
				throw new ExcelException("导入模板存在空行！");
			}
		}
		return rowList;
	}
	
	/**
	 * 集合转换为实体对象
	 * @param rowData
	 * @param clazz
	 * @return
	 * @throws Exception
	 */
	private static BeanModel list2Entity(List<Object> rowData,Class<? extends BeanModel> clazz) throws Exception{
		if(rowData!=null && rowData.size()>0){
			BeanModel t = EntityKit.newInstance(clazz);
			List<String> fields = FieldTitleHelp.getAnnotationFields(clazz);
			for(int i=0;i<fields.size();i++){
				String fieldName = fields.get(i);
				Object value = rowData.get(i);
				EntityKit.setValue(t, fieldName, value);
			}
			return t;
		}
		return null;
	}
	
	
	/**
	 * 获取单元格的值
	 * @param paramHSSFCell
	 * @return
	 */
	public static Object getCellValue( HSSFCell cell ){
		DecimalFormat df = new DecimalFormat("#");
		if (cell == null)
			return "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			if(HSSFDateUtil.isCellDateFormatted(cell)){
				return HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
			}
			return df.format(cell.getNumericCellValue());
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case HSSFCell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		case HSSFCell.CELL_TYPE_ERROR:
			return cell.getErrorCellValue();
		}
		return "";
	}
	
	public boolean checkExcelTitle(List<Object> titleList,List<Object> excelTitleList){
		boolean flag = false;
		if(titleList!=null && excelTitleList!=null){
			if(titleList.size()==excelTitleList.size()){
				for(int i=0;i<titleList.size();i++){
					String title = StringKit.null2o(titleList.get(i));
					String excelTitle = StringKit.null2o(excelTitleList.get(i));
					if(!StringKit.equals(title, excelTitle)){
						return flag;
					}
				}
				return true;
			}
		}
		return flag;
	}
	
	public static void main(String[] args) {
		List<Object> a = Lists.newArrayList();
		a.add("1");
		a.add("2");
		a.add("3");
		a.add("4");
		List<Object> b = Lists.newArrayList();
		b.add("1");
		b.add("2");
		b.add("3");
		b.add("4");
		
		System.out.println(a.equals(b));
	}
	
}
